DBMS-SQL And PL/SQL- 2.3 -Assignment-2 SPPU (Insert, Select, Update, Delete, operators, functions, set operator, all constraints, synonym, sequence)

SQL Queries:

a. Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as Table, View, Index, Sequence, Synonym, different constraints etc.

b. Write at least 10 SQL queries on the suitable database application using SQL DML

statements.

Note: Instructor will design the queries which demonstrate the use of concepts like Insert, Select,

Update, Delete with operators, functions, and set operator etc.

Assignment (Insert, Select, Update, Delete, operators, functions, setoperator, all constraints, synonym, sequence)

Create following tables with appropriate constraints like primary key, foreign key, check constrains ,not null etc.

Account(Acc_no, branch_name,balance)

branch(branch_name,branch_city,assets)

customer(cust_name,cust_street,cust_city)

Depositor(cust_name,acc_no)

Loan(loan_no,branch_name,amount)

Borrower(cust_name,loan_no)

Solve following query:

SQL> create table branch(branch_name varchar(10) primary key,branch_city varchar(10),assets varchar(10));

Table created.

SQL> create table account(account_no number(10) primary key,branch_name varchar(10) references branch(branch_name),balance number(10));

Table created.

SQL> create table customer(cust_name varchar(10) primary key,cust_strect varchar(10),custy_city varchar(10));

Table created.

SQL> create table depositer(account_no number(10)references account(account_no),cust_name varchar(10) references customer(cust_name));

Table created.

SQL> create table loan(loan_no number(10) primarykey,branch_name varchar(10) references branch(branch_name),amount number(10));

Table created.

SQL> create table borrowera(cust_name varchar(10) references customer(cust_name),loan_no number(10) references loan);

Table created.

SQL> alter table account add check(balance>=500);

Table altered.

SQL> insert into branch values('shirdi','nagar','building');

1 row created.

SQL> insert into branch values('akluj','nagar','building');

1 row created.

SQL> insert into branch values('velapur','nagar','building');

1 row created.

SQL> insert into branch values('akrudi','pune','building');

1 row created.

SQL> insert into branch values('nigdi','pune','building');

1 row created.

SQL> insert into account values(1,'shirdi',1000);

1 row created.

SQL> insert into account values(2,'akluj',100100);

1 row created.

SQL> insert into account values(3,'velapur',100100);

1 row created.

SQL> insert into account values(4,'akrudi',15000);

1 row created.

SQL> insert into account values(5,'nigdi',1500);

1 row created.

SQL> insert into customer values('sakshi','old_house','nanded');

1 row created.

SQL> insert into customer values('shweta','sam_nivas','nanded');

1 row created.

SQL> insert into customer values('aditi','adi_nivas','lukhnow');

1 row created.

SQL> insert into customer values('priyi','sai_nivas','amravati');

1 row created.

SQL> insert into customer values('anki','new_house','hydrabad');

1 row created.

SQL> insert into depositer values(1,'sakshi');

1 row created.

SQL> insert into depositer values(2,'shweta');

1 row created.

SQL> insert into depositer values(3,'aditi');

1 row created.

SQL> insert into depositer values(4,'priyi');

1 row created.

SQL> insert into depositer values(5,'anki');

1 row created.

SQL> insert into loan values(101,'shirdi',1020000);

1 row created.

SQL> insert into loan values(102,'akluj',100500);

1 row created.

SQL> insert into loan values(103,'velapur',15550000);

1 row created.

SQL> insert into loan values(104,'akrudi',15550000);

1 row created.

SQL> insert into loan values(105,'nigdi',152000);

1 row created.

SQL> insert into borrowera values('sakshi',101);

1 row created.

SQL> insert into borrowera values('shweta',102);

1 row created.

SQL> insert into borrowera values('aditi',103);

1 row created.

SQL> insert into borrowera values('priyi',104);

1 row created.

SQL> insert into borrowera values('anki',105);

1 row created.

SQL> select * from account;

ACCOUNT_NO BRANCH_NAM BALANCE

---------- ---------- ----------

1 shirdi 1000

2 akluj 100100

3 velapur 100100

4 akrudi 15000

5 nigdi 1500

SQL> select * from branch;

BRANCH_NAM BRANCH_CIT ASSETS

---------- ---------- ----------

shirdi nagar building

akluj nagar building

velapur nagar building

akrudi pune building

nigdi pune building

SQL> select * from customer;

CUST_NAME CUST_STREC CUSTY_CITY

---------- ---------- ----------

sakshi old_house nanded

shweta sam_nivas nanded

aditi adi_nivas lukhnow

priyi sai_nivas amravati

anki new_house hydrabad

SQL> select * from depositer;

ACCOUNT_NO CUST_NAME

---------- ----------

1 sakshi

2 shweta

3 aditi

4 priyi

5 anki

SQL> select * from loan;

LOAN_NO BRANCH_NAM AMOUNT

---------- ---------- ----------

101 shirdi 1020000

102 akluj 100500

103 velapur 15550000

104 akrudi 15550000

105 nigdi 152000

SQL> select * from borrowera;

CUST_NAME LOAN_NO

---------- ----------

sakshi 101

shweta 102

aditi 103

priyi 104

anki 105

Q.1)Find the names of all branches in loan relation.

SQL> select branch_name from loan group by branch_name;

BRANCH_NAM

----------

shirdi

akluj

velapur

akrudi

nigdi

Q.2)Find all loan numbers for loans made at Akurdi Branch with loan amount > 12000.

SQL> select loan_no from loan where branch_name='akrudi' and amount>1200;

LOAN_NO

----------

104

Q.3)Find all customers who have a loan from bank. Find their names, loan_no and loan amount.

SQL> select A.loan_no , A.branch_name , A.amount ,B.cust_name from loan A inner join borrower B

on A.loan_no = B.loan_no;

LOAN_NO BRANCH_NAM AMOUNT CUST_NAME

---------- ---------- ---------- ----------

101 shirdi 1020000 sakshi

102 akluj 100500 shweta

103 velapur 15550000 aditi

104 akrudi 15550000 priyi

105 nigdi 152000 anki

Q.4)List all customers in alphabetical order who have loan from Akurdi branch.

SQL> select cust_name from borrowera where loan_no IN (select loan_no from loan where

branch_name ='akrudi')order by cust_name;

CUST_NAME

----------

priyi

Q.5)Find all customers who have an account or loan or both at bank.

SQL> select cust_name from customer union select cust_name from borrowera;

CUST_NAME

----------

sakshi

shweta

priyi

aditi

anki

Q.6)Find all customers who have both account and loan at bank.

SQL> select cust_name from customer intersect select cust_name from borrowera;

CUST_NAME

----------

sakshi

shweta

priyi

aditi

anki

Q7. Find all customer who have account but no loan at the bank.

SQL> select cust_name from customer where cust_name NOT IN (select cust_name from borrowera);

no rows selected

Q8. Find average account balance at Akurdi branch.

SQL> select avg(balance) from account where

branch_name ='akrudi';

AVG(BALANCE)

------------

15000

Q9. Find the average account balance at each branch

SQL> select branch_name , avg(balance) from

account group by branch_name;

BRANCH_NAM AVG(BALANCE)

---------- ------------

shirdi 1000

akluj 100100

velapur 100100

akrudi 15000

nigdi 1500

Q.10)Find no. of depositors at each branch.

SQL> select distinct branch_name,count(account_no) from account where account_no IN(select account_no from depositer) group by branch_name;

BRANCH_NAM COUNT(ACCOUNT_NO)

---------- -----------------

shirdi 1

akluj 1

velapur 1

akrudi 1

nigdi 1

Q.11)Find the branches where average account balance > 12000.

SQL> select branch_name from account having avg(balance)>12000 group by branch_name;

BRANCH_NAM

----------

akluj

velapur

akrudi

Q12. Find number of tuples in customer relation.

SQL> select count(*) from customer;

COUNT(*)

----------

Q13. Calculate total loan amount given by bank.

SQL> select branch_name , sum(amount) from loan group by branch_name;

BRANCH_NAM SUM(AMOUNT)

---------- -----------

shirdi 1020000

akluj 100500

velapur 15550000

akrudi 15550000

nigdi 152000

Q.14) Delete all loans with loan amount between 1300 and 1500.

SQL> update loan set amount = 1400 where loan_no= 102;

1 row updated.

SQL> select * from loan;

LOAN_NO BRANCH_NAM AMOUNT

---------- ---------- ----------

101 shirdi 1020000

102 akluj 1400

103 velapur 15550000

104 akrudi 15550000

105 nigdi 152000

SQL> delete from borrowera where loan_no IN (select loan_no from loan where amount between

1300 AND 1500);

1 row deleted.

SQL> select * from borrowera;

CUST_NAME LOAN_NO

---------- ----------

sakshi 101

aditi 103

priyi 104

anki 105

SQL> delete from loan where amount between 1300

AND 1500;

1 row deleted.

SQL> select * from loan;

LOAN_NO BRANCH_NAM AMOUNT

---------- ---------- ----------

101 shirdi 1020000

103 velapur 15550000

104 akrudi 15550000

105 nigdi 152000

Q.16. Create synonym for customer table as cust.

SQL> create synonym cust for customer;

Synonym created.

SQL> select * from cust;

CUST_NAME CUST_STREC CUSTY_CITY

---------- ---------- ----------

sakshi old_house nanded

shweta sam_nivas nanded

aditi adi_nivas lukhnow

priyi sai_nivas amravati

anki ss new_house hydrabad

Q.17. Create sequence roll_seq and use in student table for roll_no column.

SQL> create sequence roll_seq start with 1

increment by 1 maxvalue 5 nocycle;

Sequence created.

SQL> create table stud123 (roll number(10) , name

varchar(10));

Table created.

SQL> insert into stud123

values(roll_seq.nextval,'priyi');

1 row created.

SQL> select * from stud123;

ROLL NAME

---------- ----------

1 priyi

-Student Reviews-

Thank you Sir your website cwipedia is very helpful

Nilesh

👍👍This site is very usefull all the questions and answers all very properly arranged

Unknown

This site is very useful //helful for practice the MCQ question and answer

Namrata

It is very useful thank you being here

Dhanashree

so useful site, seaved me from exams thanks

Shruti

Thanks you sir you are doing great job for students...... Please keep it up its really helpful for all students I also suggested my all friends to refer these mcqs for exam preparation.......

Shivam

Thank you for your quick response..

Payal

Thank you very much, so usefull site for diploma students

Very good site 👍🏻

Anirudh

50,000+ Reviews